	package dao_Impl;

import interfaces.DALException;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import connector.Connector;

import dto_objects.UserDTO;

public class MySQLUserDAO{
	
	public UserDTO login(String login, String password) throws DALException{
		ResultSet rs = Connector.doQuery("SELECT * FROM Users WHERE loginName = \"" + login + "\" AND password = \"" + password + "\"");
		try {
			if (!rs.first()) 
				throw new DALException("Wrong Login or Password");
			return getUserByUsrID(rs.getInt(1));
		}
		catch (SQLException e){
			throw new DALException(e);
		}
	}
	 
	public List<UserDTO> getUsersByRigths(UserDTO user) throws DALException {
		List<UserDTO> list = new ArrayList<UserDTO>();
		ResultSet rs = Connector.doQuery("SELECT * FROM Users WHERE rights = '" + user.getRights() + "'");
		try {
			if (!rs.first()) throw new DALException("User right" + user.getRights() + " does not excist");
			while (rs.next()) {
				list.add(new UserDTO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4)));
			}
		} catch (SQLException e) {
			throw new DALException(e);
		}
		return list;
	}

	public void createUser(UserDTO user) throws DALException {
		Connector.doUpdate(	
				"INSERT INTO Users VALUES (" + user.getUsrID() + ",'" + user.getRights() + "' , '" + user.getLoginName() + "' , '" + user.getPassword() + "')"+ "; "
				);
		Connector.doUpdate(	
				"INSERT INTO User_Data VALUES (" + user.getUsrID() + ",'" + user.getCPR() + "' , '" + user.getName() + "' , '" + user.getAddress() + 
				 "' , '" + user.getZipcode() + "' , '" + user.getPhone() + "' , '" + user.getEmail() +"')"
				);
		
	}

	public void updateUser(UserDTO user) throws DALException {
		Connector.doUpdate(
				"UPDATE Users" +
				" SET usrID = '" + user.getUsrID() +" rights = '" + user.getRights() + "', loginName = '" + user.getLoginName() + "', password = '" + user.getPassword() + "'" +
				" WHERE usrID =" + user.getUsrID()
			);
		Connector.doUpdate(
				"UPDATE User_Data" +
				" SET usrID = '" + user.getUsrID() + "', CPR = '" + user.getCPR() + "', name = '" + user.getName() + "'" + "', address = '" + user.getAddress() + "'" +
				"', zipcode = '" + user.getZipcode() + "', phone = '" + user.getPhone() + "'" + "', email = '" + user.getEmail() + "'" +
				" WHERE usrID =" + user.getUsrID()
			);
	}
	
	public void deleteUser(UserDTO user) throws DALException {
		Connector.doUpdate(
				"UPDATE Users SET rights = 'deleted' WHERE usrID = " + user.getUsrID()
			);
	}

	public UserDTO getUserByUsrID(int userID) throws DALException {
		ResultSet rs = Connector.doQuery("SELECT * FROM Users NATURAL JOIN User_Data WHERE usrID = " + userID);
		try {
			if (!rs.first()) throw new DALException("UserID: " + userID + " does not exist");
			return new UserDTO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getString(6), rs.getString(7), rs.getInt(8), rs.getInt(9), rs.getString(10));

		}
		catch (SQLException e){
			throw new DALException(e);
		}
	}
	
	public int getMaxUserID() throws DALException,SQLException {
		ResultSet rs = Connector.doQuery("SELECT MAX(usrID) FROM Users");
		if (!rs.first()) throw new DALException("No fakturas exsist");
			return rs.getInt(1);
	}


}
